package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Student;
import cn.edu.sdjzu.xg.kcsj.domain.StudentClass;
import cn.edu.sdjzu.xg.kcsj.service.StudentClassService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class StudentDao {
    private static StudentDao studentDao = null;
    private StudentDao() {
    }
    public static StudentDao getInstance() {

        // 静态变量StudentDao为空时，才创建StudentDao对象。
        // 第二次调用本方法StudentDao就不空了，不再创建对象。
        if (studentDao == null) {
            studentDao = new StudentDao();
        }
        return studentDao;
    }

    /**
     * @param connection    数据库连接对象
     * @param conditionList 查询条件
     * @param pagination    分页对象
     * @return desiredStudents
     * @throws SQLException
     */
    public Collection<Student> findAll(Pagination pagination, List<Condition> conditionList, Connection connection) throws SQLException {

        //创建Student类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Student> desiredStudents = new HashSet<Student>();

        //创建查询学生的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM student");

        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }

        //获取Student中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Student");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }

        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_select_student =
                connection.prepareStatement(sql_select.toString());


        //执行预编译语句
        ResultSet resultSet_select = pstmt_select_student.executeQuery();

        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            Integer age = resultSet_select.getInt ("age");
            String sex = resultSet_select.getString("sex");
            Integer studentClass_id = resultSet_select.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            Student desiredStudent = new Student(id, name, no, age,sex,studentClass);
            desiredStudents.add(desiredStudent);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select_student);
        return desiredStudents;
    }

    /**
     * @param connection 数据库连接对象
     * @param id         查询条件
     * @return desiredStudent
     * @throws SQLException
     */
    public Student find(Integer id, Connection connection) throws SQLException {

        //创建Student类型的引用，暂不创建对象
        Student desiredStudent = null;

        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_select_student_id =
                connection.prepareStatement("SELECT * FROM Student WHERE id = ?");
        //为预编译参数赋值
        pstmt_select_student_id.setInt(1, id);
        ResultSet resultSet_select_student_id = pstmt_select_student_id.executeQuery();

        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        while (resultSet_select_student_id.next()) {
            Integer idToLogin = resultSet_select_student_id.getInt ("id");
            String name = resultSet_select_student_id.getString("name");
            String no = resultSet_select_student_id.getString("no");
            Integer age = resultSet_select_student_id.getInt ("age");
            String sex = resultSet_select_student_id.getString("sex");
            Integer studentClass_id = resultSet_select_student_id.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            desiredStudent = new Student(idToLogin, name, no, age,sex,studentClass);
        }

        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select_student_id, pstmt_select_student_id);
        return desiredStudent;
    }

    /**
     * 由于Student扩展了Actor，前者对象id取后者的id的值，故参数student是有id的
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     *
     * @param student    要增加到数据库表中的Student对象
     * @param connection 来自Service的Connection对象
     * @throws SQLException
     */
    public Boolean add(Student student, Connection connection) throws SQLException {

        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_add =
                connection.prepareStatement(
                        "INSERT INTO student " +
                                " (id, name,no,age, sex,studentClass_id)" +
                                " VALUES (?,?,?,?,?,?)");
        //为预编译参数赋值
        pstmt_add.setInt(1, student.getId());
        pstmt_add.setString(2, student.getName());
        pstmt_add.setString(3, student.getNo());
        pstmt_add.setInt (4,student.getAge ());
        pstmt_add.setString (5,student.getSex ());
        pstmt_add.setInt(6, student.getStudentClass().getId());
        int affectedRowNum =pstmt_add.executeUpdate();

        //本方法是事务的一部分，不能释放数据库连接
        JdbcHelper.close(null,pstmt_add);
        if (  affectedRowNum == 1) {
            return true;
        }else {
            return false;
        }
    }

    /**
     * @param connection 数据库连接对象
     * @param id         删除条件
     * @return 影响行数（是否删除成功）
     * @throws SQLException
     */
    public boolean delete(Integer id, Connection connection) throws SQLException {

        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_delete =
                connection.prepareStatement(
                        "DELETE FROM student WHERE id = ?");

        //为预编译参数赋值
        pstmt_delete.setInt(1, id);
        int affectedRowNum = pstmt_delete.executeUpdate();

        JdbcHelper.close(null,pstmt_delete);
        return affectedRowNum > 0;
    }

    /**
     * @param connection 数据库连接对象
     * @param student    修改的目标对象
     * @return 影响行数（是否修改成功）
     */
    public boolean update(Student student, Connection connection) throws SQLException {

        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_update = connection.prepareStatement(
                "UPDATE student SET " +
                        "name = ?,no = ?,age = ?,sex = ?,studentClass_id = ? WHERE id = ?");

        //为预编译参数赋值
        pstmt_update.setString(1, student.getName());
        pstmt_update.setString(2, student.getNo());
        pstmt_update.setInt (3,student.getAge ());
        pstmt_update.setString (4,student.getSex ());
        pstmt_update.setInt(5, student.getStudentClass().getId());
        pstmt_update.setInt(6, student.getId());

        int affectedRowNum = pstmt_update.executeUpdate();
        JdbcHelper.close(null,pstmt_update);
        return affectedRowNum > 0;
    }
}
